﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Web.Configuration;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace Perm.Web
{
    [ServiceContract(Namespace = "")]
    [SilverlightFaultBehavior]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class RaitingMaxMin
    {
        bool raedBool = false;
        string select = "", 
               String = "", 
               error = null;
        double 
            maxRoad = 0, minRoad = 0,
            maxGAS = 0, minGAS = 0,
            maxCarwash = 0, minCarwash = 0,
            commentsCount = 0;
        
        SqlConnection conMSSQL;
        SqlDataReader readerMSSQL;
        SqlCommand commandMSSQL;

        DateTime thisDate = DateTime.Now.ToUniversalTime().AddHours(6);

        List<double> list = new List<double>();

        [OperationContract]
        public List<double> DoWork(string city)
        {            
            string connectionStringMSSQL = WebConfigurationManager.ConnectionStrings["MSSQL"].ConnectionString;
                try {
                    try {
                        conMSSQL = new SqlConnection(connectionStringMSSQL);                    
                        conMSSQL.Open();
                    } catch { error += " Невозможно подключиться к базе данных. MS SQL (Rating)"; }
                                   
                    switch (city)
                    {
                        case "perm":
                            {
                                select = "SELECT TOP 1 (SELECT Max(RATING_ROAD_PERM.RATING) AS [Max-RATING] FROM  RATING_ROAD_PERM) AS maxRoad, (SELECT Min(RATING_ROAD_PERM.RATING) AS [Max-RATING] FROM  RATING_ROAD_PERM) AS minRoad, (SELECT Max(RATING_GAS_PERM.RATING) AS [Max-RATING] FROM  RATING_GAS_PERM) AS maxGas,  (SELECT Min(RATING_GAS_PERM.RATING) AS [Max-RATING] FROM  RATING_GAS_PERM) AS minGas, (SELECT Max(RATING_CARWASH_PERM.RATING) AS [Max-RATING] FROM  RATING_CARWASH_PERM) AS maxCarwash,  (SELECT Min(RATING_CARWASH_PERM.RATING) AS [Max-RATING] FROM  RATING_CARWASH_PERM) AS minCarwash FROM RATING_ROAD_PERM, RATING_GAS_PERM, RATING_CARWASH_PERM";
                                break;
                            }
                        case "ekb":
                            {
                                select = "SELECT TOP 1 (SELECT Max(RATING_ROAD_EKB.RATING) AS [Max-RATING] FROM  RATING_ROAD_EKB) AS maxRoad, (SELECT Min(RATING_ROAD_EKB.RATING) AS [Max-RATING] FROM  RATING_ROAD_EKB) AS minRoad, (SELECT Max(RATING_GAS_EKB.RATING) AS [Max-RATING] FROM  RATING_GAS_EKB) AS maxGas,  (SELECT Min(RATING_GAS_EKB.RATING) AS [Max-RATING] FROM  RATING_GAS_EKB) AS minGas, (SELECT Max(RATING_CARWASH_EKB.RATING) AS [Max-RATING] FROM  RATING_CARWASH_EKB) AS maxCarwash,  (SELECT Min(RATING_CARWASH_EKB.RATING) AS [Max-RATING] FROM  RATING_CARWASH_EKB) AS minCarwash FROM RATING_ROAD_EKB, RATING_GAS_EKB, RATING_CARWASH_EKB";
                                break;
                            }
                        case "permkray":
                            {
                                select = "SELECT TOP 1 (SELECT Max(RATING_ROAD_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_ROAD_PERMKRAY) AS maxRoad, (SELECT Min(RATING_ROAD_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_ROAD_PERMKRAY) AS minRoad, (SELECT Max(RATING_GAS_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_GAS_PERMKRAY) AS maxGas,  (SELECT Min(RATING_GAS_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_GAS_PERMKRAY) AS minGas, (SELECT Max(RATING_CARWASH_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_CARWASH_PERMKRAY) AS maxCarwash,  (SELECT Min(RATING_CARWASH_PERMKRAY.RATING) AS [Max-RATING] FROM  RATING_CARWASH_PERMKRAY) AS minCarwash FROM RATING_ROAD_PERMKRAY, RATING_GAS_PERMKRAY, RATING_CARWASH_PERMKRAY";
                                break;
                            }
                    }  

                    try
                    {
                        commandMSSQL = new SqlCommand(select, conMSSQL);
                        readerMSSQL = commandMSSQL.ExecuteReader();
                        readerMSSQL.Read();

                        maxRoad = readerMSSQL.GetDouble(0);
                        minRoad = readerMSSQL.GetDouble(1);
                        maxGAS = readerMSSQL.GetDouble(2);
                        minGAS = readerMSSQL.GetDouble(3);
                        maxCarwash = readerMSSQL.GetDouble(4);
                        minCarwash = readerMSSQL.GetDouble(5);
                    }
                    catch { error += " Невозможно получить максимальные и минимальные данные."; }

                    switch (city)
                    {
                        case "perm":
                            {
                                select = "SELECT Count(COMMENTS.[NO]) FROM COMMENTS WHERE COMMENTS.[NO] = 0";
                                break;
                            }
                        case "ekb":
                            {
                                select = "SELECT Count(COMMENTS_EKB.[NO]) FROM COMMENTS_EKB WHERE COMMENTS_EKB.[NO] = 0";
                                break;
                            }
                        case "permkray":
                            {
                                select = "SELECT Count(COMMENTS_PERMKRAY.[NO]) FROM COMMENTS_PERMKRAY WHERE COMMENTS_PERMKRAY.[NO] = 0";
                                break;
                            }
                    }

                    try
                    {
                        commandMSSQL = new SqlCommand(select, conMSSQL);
                        readerMSSQL = commandMSSQL.ExecuteReader();
                        readerMSSQL.Read();

                        commentsCount = (readerMSSQL[0].ToString() != "") ? (double)readerMSSQL.GetInt32(0) : 0;
                    }
                    catch { error += " Невозможно получить количество комментариев."; }

                    list.Add(maxRoad);
                    list.Add(minRoad);
                    list.Add(maxGAS);
                    list.Add(minGAS);
                    list.Add(maxCarwash);
                    list.Add(minCarwash);
                    list.Add(commentsCount);

                    if (error != null)
                    {
                        switch (city)
                        {
                            case "perm":
                                {
                                    String = "INSERT INTO ERRORS (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                    break;
                                }
                            case "ekb":
                                {
                                    String = "INSERT INTO ERRORS_EKB (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                    break;
                                }
                            case "permkray":
                                {
                                    String = "INSERT INTO ERRORS_PERMKRAY (DATA, ERROR) VALUES ('" + thisDate + "', '" + error + "')";
                                    break;
                                }
                        }
                        try
                        {
                            commandMSSQL = new SqlCommand(String, conMSSQL);
                            commandMSSQL.ExecuteNonQuery();
                        }
                        catch { }
                    }
                    conMSSQL.Close();
                }
                catch { }
            return list;
        }       

        // Добавьте здесь дополнительные операции и отметьте их атрибутом [OperationContract]
    }
}
